package com.lanhao.aot.excel;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Random;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import org.apache.commons.lang3.mutable.MutableObject;
import org.springframework.util.StopWatch;

import com.google.common.collect.Lists;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

public class ExcelDataProcessUtil {
	public static Random random = new Random();
	public static String BaseDir = "D:/";
	public static String dataFilePath = BaseDir + "data.xlsx";
	public static int everyDayNum = 70000;
	public static void main(String[] args) {
		ExecutorService tp = Executors.newFixedThreadPool(31);
		List<ExcelData> dataList = Lists.newArrayList();
		StopWatch sw = new StopWatch();
		sw.start();
		for (int i = 0; i < 5; i++) {
			System.out.println("开始第" + (i + 1) + "个sheet数据汇聚...");
			ExcelReader reader = ExcelUtil.getReader(dataFilePath, i);
			List<List<Object>> allDataList = reader.read();
			for (int j = 2; j < allDataList.size(); j++) {
				List<Object> list = allDataList.get(j);
				String weight = (String) list.get(1);
				String zipCode = (String) list.get(7);
				dataList.add(new ExcelData(weight, zipCode));
			}
			System.out.println("完成第" + (i + 1) + "个sheet数据汇聚。");
		}
		sw.stop();
		System.out.println("数据汇聚完成，总条数totalSize=" + dataList.size() + "，耗时cost=" + sw.getTotalTimeSeconds() + "s");
		for (int i = 0; i < 5; i++) {
			MutableObject<Integer> index = new MutableObject<>();
			index.setValue(i);
			System.out.println("正在处理第" + i + "个文件");
			tp.execute(() -> {
				Integer idx = index.getValue();
				StopWatch sw1 = new StopWatch();
				sw1.start();
				List<ExcelData> randomList = processRandomList(idx, randomList(idx, dataList));
				// 通过工具类创建writer
				ExcelWriter writer = ExcelUtil.getWriter(BaseDir + "data_" + idx + "_补充.xlsx");
				// 合并单元格后的标题行，使用默认标题样式
				// writer.merge(4, "一班成绩单");
				// 一次性写出内容，使用默认样式，强制输出标题
				writer.write(randomList, true);
				// 关闭writer，释放内存
				writer.close();
				sw1.stop();
				System.out.println("第" + idx + "个文件处理完成，耗时cost=" + sw1.getTotalTimeSeconds() + "s");
			});
		}

	}
	
	public static List<ExcelData> randomList(int idx, List<ExcelData> dataList) {
		int size = dataList.size();
		Random rand = new Random();
		Set<ExcelData> set = new HashSet<>();
		int i = 1;
		while (i <= everyDayNum) {
			ExcelData data = dataList.get(rand.nextInt(size - 1) + 1);
			set.add(data);
			i++;
		}
		List<ExcelData> list = new ArrayList<ExcelData>(set);
		return list;
	}
	
	public static List<ExcelData> processRandomList(int idx, List<ExcelData> randomList) {
		List<ExcelData> list = new ArrayList<ExcelData>();
		for (int i = 0; i < randomList.size(); i++) {
			ExcelData excelData = randomList.get(i);
			String result = randomFrom(0, 1, 0.5d, 1d); // 10%
			int step = everyDayNum / 10;
//			if(idx <= 5) {
//				result = BigDecimalUtil.getBigDecimal(new BigDecimal(BigDecimalUtil.add(Double.valueOf(excelData.getWeight()), random.nextDouble()))).toPlainString();
//			} else {
				if (i <= step * 1) {
					result = randomFrom(0, 1, 0d, 0.1d); // 10%
				} else if (i > step * 1 && i <= step * 3) {
					result = randomFrom(0, 1, 0.1d, 0.3d); // 20%
				} else if (i > step * 3 && i <= step * 5) {
					result = randomFrom(0, 1, 0.3, 0.5); // 20%
				} else if (i > step * 5 && i <= step * 6) {
					result = randomFrom(1, 3, null, null); // 10%
				} else if (i > step * 6 && i <= step * 7) {
					result = randomFrom(3, 5, null, null); // 10%
				} else if (i > step * 7 && i <= step * 8) {
					result = randomFrom(5, 10, null, null); // 10%
				} else if (i > step * 8 && i <= step * 9) {
					result = randomFrom(10, 20, null, null); // 10%
				} else if (i > step * 9 && i <= step * 10) {
					result = randomFrom(20, 50, null, null); // 10%
				}
//			}
			excelData.setWeight(result);
			list.add(excelData);
		}
		return list;
	}
	
	public static String randomFrom(int min, int max, Double start, Double end) {
		Random rand = new Random();
		double value = rand.nextDouble() * (max - min) + min;
		if (start != null && end != null) {
			if (value < start || value >= end) {
				return randomFrom(min, max, start, end);
			}
		}
		String result = new BigDecimal(value).setScale(2, BigDecimal.ROUND_HALF_DOWN).toPlainString();
		return result;
	}
}
